import sqlite3 as sql
from inopy.objects.snip import enum

class DB(object):
    FIELD_TYPES = enum(TEXT='text', INTEGER='integer', FLOAT='float', BLOB = 'blob', NULL = 'null')
    
    def __init__(self, path = ':memory:'):
        self.con = sql.connect(path)
        self.cur = self.con.cursor()

    def create_table(self, table_name, **fields):
        cmd = 'CREATE TABLE %s (%s)'%(table_name, ','.join([k + ' ' + fields[k] for k in fields.keys()]))
        self.cur.execute(cmd)
        return cmd

    def __getitem__(self, table):
        return DB_Table(table, self.cur)

    def __setitem__(self, item, value):
        DB_Table(item, self.cur).execute(value)

    def last_select(self):
        return self.cur.fetchall()
    
    def close(self):
        self.cur.close()
        self.con.close()

    def _dba_execute(self, cmd):
        self.cur.execute(cmd)

    def get_tables(self):
        self._dba_execute("select * from sqlite_master where type='table'")
        return [x[1] for x in self.last_select()]


class DB_Table(object):
    def __init__(self, name, cur):
        self.name = name
        self.cur = cur

    def execute(self, cmd):
        cmd = cmd.lower().replace('from this', 'from ' + self.name)
        cmd = cmd.lower().replace('from self', 'from ' + self.name)
        cmd = cmd.lower().replace('insert into this', 'insert into ' + self.name)
        cmd = cmd.lower().replace('insert into self', 'insert into ' + self.name)
        cmd = cmd.lower().replace('update this', 'update ' + self.name)
        cmd = cmd.lower().replace('update self', 'update ' + self.name)
        cmd = cmd.lower().replace('drop table this', 'drop table ' + self.name)
        cmd = cmd.lower().replace('drop table self', 'drop table ' + self.name)
        self.cur.execute(cmd)
        return cmd

    def insert(self, values):
        if isinstance(values, list) or isinstance(values, tuple):
            v = ','.join([repr(x) for x in values])
            cmd = 'insert into %s values (%s)'%(self.name, v)
        elif isinstance(values, dict):
            vitems = values.items()
            cmd = 'insert into %s (%s) values (%s)'%(self.name, ','.join([x[0] for x in vitems]), ','.join([repr(x[1]) for x in vitems]))
        self.execute(cmd)
        return cmd

    def select(self, fields, optional = ''):
        if fields == ['*']:
            fields = self.get_columns()
        cmd = 'select ' + ','.join(fields) + ' from %s %s' %(self.name, optional)
        res = {x:[] for x in fields}
        self.cur.execute(cmd)
        x = self.cur.fetchall()
        for line in x:
            for i in xrange(len(line)):
                res[fields[i]].append(line[i])
        return res

    def update(self, values, where = ''):
        cmd = 'update %s set %s'%(self.name, ','.join([str(x[0]) + "=" + repr(x[1]) for x in values.items()]))
        if where:
            cmd += ' where ' + where
        self.execute(cmd)
        return cmd

    def get_columns(self):
        cmd = "pragma table_info('%s')"%(self.name)
        self.execute(cmd)
        col = [x[1] for x in self.cur.fetchall()]
        return col
        
        
    

    

    
